« Previous
Next »
« Previous
Next »
MICROSOFT EXCEL 2013
Logical function
- IF( )Checks whether a condition is met, and returns one value if true, and a different value if false
- AND() Tests a number of user-defined conditions and returns TRUE if all of the conditions evaluate to TRUE otherwise it returns FALSE.
- OR() Tests a number of user-defined conditions and returns TRUE if any one of the conditions evaluate to TRUE otherwise it returns FALSE.
- NOT() returns TRUE value when the statements are FALSE otherwise return FALSE.
- First create the below data structure.
- Calculate Total by typing =sum(C2:E2) in Cell F2.
- Calculate Percentage of marks by typing =F2/G2*100 in Cell H2.
- Calculate grade based on the condition if percentage of marks >=30 the student will get Pass otherwise Fail.
- Type the formula in I2 Cell =IF(H2>=30,”Pass”,”Fail”) Ex: Calculate Grade of the student by using “if & and” function as below grade formats: If Percentage of marks >=70 and all individual subject marks>=50 then student will get Grade “A”. If Percentage of marks >=60 and all individual subject marks>=50 then student will get Grade “B”. If Percentage of marks >=50 and all individual subject marks>=50 then student will get Grade “C”. Otherwise “F” Grade.
- First create the below data structure.
- Calculate Total by typing =sum(C2:E2) in Cell F2.
- Calculate Percentage of marks by typing =F2/G2*100 in Cell H2.
- Calculate grade type the formula in I2 Cell =IF(AND(C2>=50,D2>=50,E2>=50,H2>=70),"A",IF(AND(C2>=50,D2>=50,E2>=50,H2>=60),"B",IF(AND(C2>=50,D2>=50,E2>=50,H2>=50),"C","F")))
- Lower() Converts all characters in supplied text string to lower case.
- Upper() Converts all characters in a supplied text string to Upper Case.
- TRIM() Removes duplicate spaces and spaces at the start and end of a text string.
- CONCATENATE() Joins together two or more text strings
- LEFT() Returns a specified number of characters from the start of a supplied text string.
- MID() Returns a specified number of characters from the middle of a supplied text string.
- RIGHT() Returns a specified number of characters from the end of a supplied text string.
- LEN( )Returns the number of characters in some text
- TODAY( )Returns the current date
- NOW( )Returns the current date and time
- DATE() Returns a date, from a user-supplied year, month and day
- TIME() Returns a time, from a user-supplied hour, minute and second
- COUNT( )Counts all the cells that contain numbers in a range of cells
- AVERAGE( )Calculates the average in a range of cells
- MEDIAN( )Calculates the median in a range of cells
- MODE( )Calculates the mode in a range o \f cells
- MAX( )Returns the largest number in a range of cells
- MIN( )Returns the smallest number in a range of cells
- MOD( )Returns the remainder when a number is divided by a divisor
Ex: Calculate Grade of the student by using “if” function.
